unit SalaryForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBModule, Vcl.ExtCtrls, Vcl.ComCtrls,
  Vcl.StdCtrls, Vcl.Grids, Vcl.DBGrids, Vcl.DBCtrls, frxClass, frxDBSet, IB,
  UserUtils, System.DateUtils, frxExportPDF, Data.DB, IBCustomDataSet, IBQuery,
  IBTable;

type
  TfmSalary = class(TForm)
    DBGrid1: TDBGrid;
    Label1: TLabel;
    GroupBox1: TGroupBox;
    Label2: TLabel;
    Label3: TLabel;
    txtYear: TEdit;
    Label4: TLabel;
    txtMonth: TEdit;
    Label5: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    txtAdvanced: TEdit;
    Label11: TLabel;
    txtAllowance: TEdit;
    UpDown1: TUpDown;
    UpDown2: TUpDown;
    lblEPFEmployee: TLabel;
    lblEPFEmployer: TLabel;
    lblSOCSOEmployee: TLabel;
    lblSOCSOEmployer: TLabel;
    cbName: TDBLookupComboBox;
    btnNew: TButton;
    btnEdit: TButton;
    btnSave: TButton;
    btnDelete: TButton;
    btnClose: TButton;
    Label13: TLabel;
    mmRemark: TMemo;
    GroupBox2: TGroupBox;
    txtYearFilter: TEdit;
    UpDown3: TUpDown;
    txtMonthFilter: TEdit;
    UpDown4: TUpDown;
    cbFilterByYear: TCheckBox;
    cbFilterByMonth: TCheckBox;
    frxDBDataset1: TfrxDBDataset;
    frxReport1: TfrxReport;
    btnPreview: TButton;
    Label12: TLabel;
    lblNetPay: TLabel;
    Label6: TLabel;
    Label16: TLabel;
    lblPCB: TLabel;
    lblBasicSalary: TLabel;
    btnPrint: TButton;
    Label17: TLabel;
    txtWorkingDays: TEdit;
    frxPDFExport1: TfrxPDFExport;
    IBQuery1: TIBQuery;
    frxDBConfig: TfrxDBDataset;
    IBtbConfig: TIBTable;
    procedure FormCreate(Sender: TObject);
    procedure cbNameCloseUp(Sender: TObject);
    procedure txtAdvancedKeyPress(Sender: TObject; var Key: Char);
    procedure txtAllowanceKeyPress(Sender: TObject; var Key: Char);
    procedure txtAdvancedChange(Sender: TObject);
    procedure txtAllowanceChange(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
    procedure btnEditClick(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure btnDeleteClick(Sender: TObject);
    procedure btnCloseClick(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure cbFilterByYearClick(Sender: TObject);
    procedure cbFilterByMonthClick(Sender: TObject);
    procedure txtYearFilterChange(Sender: TObject);
    procedure txtMonthFilterChange(Sender: TObject);
    procedure btnPreviewClick(Sender: TObject);
    procedure Label15Click(Sender: TObject);
    procedure Label14Click(Sender: TObject);
    procedure btnPrintClick(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure cbNameDropDown(Sender: TObject);
    procedure txtWorkingDaysKeyPress(Sender: TObject; var Key: Char);
    procedure txtWorkingDaysChange(Sender: TObject);
    function frxReport1UserFunction(const MethodName: string;
      var Params: Variant): Variant;
  private
    { Private declarations }
    Operation: TOperation;
    CurrentEmployeeID: integer;
    procedure FillDetails;
    procedure ClearDetails;
    procedure InsertDetails;
    procedure UpdateDetails;
    procedure AutoCalculate;
    procedure SetFilter;
    procedure SetPrintFilter;
  public
    { Public declarations }
  end;

var
  fmSalary: TfmSalary;

implementation

{$R *.dfm}

procedure TfmSalary.btnNewClick(Sender: TObject);
begin
  ClearDetails;
end;

procedure TfmSalary.btnEditClick(Sender: TObject);
begin
  FillDetails;
end;

procedure TfmSalary.btnSaveClick(Sender: TObject);
begin
  if Operation = opNew then
    InsertDetails
  else
    UpdateDetails;
  ClearDetails;
end;

procedure TfmSalary.btnPreviewClick(Sender: TObject);
begin
  SetPrintFilter;
  frxReport1.LoadFromFile('PaySlip.fr3');
  frxReport1.ShowReport();
end;

procedure TfmSalary.btnPrintClick(Sender: TObject);
begin
  SetPrintFilter;
  frxReport1.LoadFromFile('PaySlip.fr3');
  frxReport1.PrepareReport();
  frxReport1.Print;
end;

procedure TfmSalary.btnDeleteClick(Sender: TObject);
begin
  if MessageDlg('Confirm delete?', mtWarning, mbYesNo, 0) = mrYes then
  begin
    try
      with DataModule1.qrySQL do
      begin
        try
          SQL.Clear;
          Params.Clear;
          SQL.Add('delete from PAY_SALARY where ID=:ID');
          Params.ParamByName('ID').AsInteger := DBGrid1.DataSource.DataSet.FieldByName('PAY_ID').AsInteger;
          if not Prepared then
            Prepare;
          ExecSQL;
          Transaction.Commit;
        except
          on E: EIBInterBaseError do
          begin
            Transaction.Rollback;
            ShowMessage(Format('On Delete Button Click: IBErrorCode=%d, SQLCode=%d, Msg=%s', [E.IBErrorCode, E.SQLCode, E.Message]));
          end;
        end;
      end;
    finally
      with DataModule1.fbEmployee do
        if not Active then
          Open;
      with DataModule1.qryEmployeeSalary do
        if not Active then
          Open;
      ClearDetails;
    end;
  end;
end;

procedure TfmSalary.btnCloseClick(Sender: TObject);
begin
  Close;
end;

procedure TfmSalary.cbFilterByMonthClick(Sender: TObject);
begin
  SetFilter;
  txtMonthFilter.SetFocus;
end;

procedure TfmSalary.cbFilterByYearClick(Sender: TObject);
begin
  SetFilter;
  txtYearFilter.SetFocus;
end;

procedure TfmSalary.cbNameCloseUp(Sender: TObject);
begin
  lblBasicSalary.Caption := Format('%.2f', [cbName.ListSource.DataSet.FieldByName('BASE_PAY').AsFloat]);
  CurrentEmployeeID := cbName.ListSource.DataSet.FieldByName('ID').AsInteger;
  AutoCalculate;
end;

procedure TfmSalary.cbNameDropDown(Sender: TObject);
begin
  with cbName.ListSource.DataSet do
  begin
    if not Active then
      Open;
    Last;
    First;
  end;
end;

procedure TfmSalary.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  FreeAndNil(fmSalary);
end;

procedure TfmSalary.FormCreate(Sender: TObject);
begin
  with DataModule1 do
  begin
    if not fbEmployee.Active then
      fbEmployee.Open;
    if not qryEmployeeSalary.Active then
      qryEmployeeSalary.Open;
    if not fbConfig.Active then
      fbConfig.Open;
  end;
  ClearDetails;
  frxReport1.AddFunction('function FileExist(s:string): Boolean;');
  frxReport1.AddFunction('function StrReplace(s:string; p1:string; p2:string): string;');
end;

function TfmSalary.frxReport1UserFunction(const MethodName: string;
  var Params: Variant): Variant;
begin
  if MethodName = 'FILEEXIST' then
    Result := FileExists(Params[0])
  else if MethodName = 'STRREPLACE' then
    Result := StringReplace(Params[0], Params[1], Params[2], [rfReplaceAll]);
end;

procedure TfmSalary.txtAdvancedChange(Sender: TObject);
begin
  AutoCalculate;
end;

procedure TfmSalary.txtAdvancedKeyPress(Sender: TObject; var Key: Char);
begin
  case Key of
    '0'..'9':;
    '.':;
    Char(VK_BACK):;
    else
      Key := #0;
  end;
end;

procedure TfmSalary.txtAllowanceChange(Sender: TObject);
begin
  AutoCalculate;
end;

procedure TfmSalary.txtAllowanceKeyPress(Sender: TObject; var Key: Char);
begin
  case Key of
    '0'..'9':;
    '.':;
    Char(VK_BACK):;
    else
      Key := #0;
  end;
end;

procedure TfmSalary.txtMonthFilterChange(Sender: TObject);
begin
  if Active and cbFilterByMonth.Checked then
    SetFilter;
end;

procedure TfmSalary.txtWorkingDaysChange(Sender: TObject);
begin
  AutoCalculate;
end;

procedure TfmSalary.txtWorkingDaysKeyPress(Sender: TObject; var Key: Char);
begin
  case Key of
    '0'..'9':;
    '.':;
    Char(VK_BACK):;
    else
      Key := #0;
  end;
end;

procedure TfmSalary.txtYearFilterChange(Sender: TObject);
begin
  if Active and cbFilterByYear.Checked then
    SetFilter;
end;

procedure TfmSalary.ClearDetails;
begin
  if not cbName.ListSource.DataSet.Active then
    cbName.ListSource.DataSet.Open;
  cbName.ListSource.DataSet.First;
  cbName.ListSource.DataSet.MoveBy(-1);
  cbName.Enabled := True;
  txtYear.Enabled := True;
  txtMonth.Enabled := True;
  UpDown1.Enabled := True;
  UpDown2.Enabled := True;
  UpDown1.Position := YearOf(Date);
  UpDown2.Position := MonthOf(Date);
  UpDown3.Position := UpDown1.Position;
  UpDown4.Position := UpDown2.Position;
  txtAdvanced.Text := '0.00';
  txtAllowance.Text := '0.00';
  lblEPFEmployee.Caption := '0.00';
  lblEPFEmployer.Caption := '0.00';
  lblSOCSOEmployee.Caption := '0.00';
  lblSOCSOEmployer.Caption := '0.00';
  lblPCB.Caption := '0.00';
  lblNetPay.Caption := '0.00';
  lblBasicSalary.Caption := '0.00';
  mmRemark.Lines.Clear;
  Operation := opNew;
  with DataModule1.fbConfig do
  begin
    txtWorkingDays.Text := FieldByName('WORKINGDAYS').AsString;
  end;

end;

procedure TfmSalary.DBGrid1CellClick(Column: TColumn);
begin
  FillDetails;
end;

procedure TfmSalary.DBGrid1TitleClick(Column: TColumn);
var
  strSQL: string;
begin
  with DataModule1.qryEmployeeSalary do
  begin
    strSQL := SQLBeforeClause(SQL.Text, 'ORDER BY') +
              'ORDER BY "' + Column.FieldName + '"';
    SQL.Clear;
    Params.Clear;
    SQL.Add(strSQL);
    if cbFilterByYear.Checked then
      Params.ParamByName('Year').AsString := txtYearFilter.Text;
    if cbFilterByMonth.Checked then
      Params.ParamByName('Month').AsString := txtMonthFilter.Text;
    if not Prepared then
      Prepare;
    Open;
  end;
end;

procedure TfmSalary.AutoCalculate;
var
  EPFPerEmployee, EPFPerEmployer: Single;
  HaveEPF, HaveSOCSO: Boolean;
  WorkingDays: Integer;
begin
  with DataModule1.fbConfig do
  begin
    EPFPerEmployee := FieldByName('EPF_PER_EMPLOYEE').AsFloat / 100;
    EPFPerEmployer := FieldByName('EPF_PER_EMPLOYER').AsFloat / 100;
    WorkingDays := FieldByName('WORKINGDAYS').AsInteger;
  end;
  if not TryStrToBool(DBGrid1.DataSource.DataSet.FieldByName('EPF').AsString, HaveEPF) then
    HaveEPF := False;
  if HaveEPF then
  begin
    lblEPFEmployee.Caption := Format('%.2f', [StrToFloat(lblBasicSalary.Caption) * EPFPerEmployee]);
    lblEPFEmployer.Caption := Format('%.2f', [StrToFloat(lblBasicSalary.Caption) * EPFPerEmployer]);
  end;
  lblNetPay.Caption := Format('%.1f0',
                              [StrToFloat(lblBasicSalary.Caption) / WorkingDays * StrToFloat(txtWorkingDays.Text) +
                              StrToFloat(txtAllowance.Text) -
                              StrToFloat(lblEPFEmployee.Caption) -
                              StrtoFloat(lblSOCSOEmployee.Caption) -
                              StrToFloat(txtAdvanced.Text) -
                              StrToFloat(lblPCB.Caption)]);
end;

procedure TfmSalary.SetFilter;
var
  strFilter: string;
  strSQL: string;
begin
  strFilter := '';
  if cbFilterByYear.Checked then
    strFilter := '"YEAR"=:Year';
  if cbFilterByMonth.Checked then
  begin
    if strFilter <> '' then
      strFilter := strFilter + ' AND ';
    strFilter := strFilter + '"MONTH"=:Month';
  end;
  with DataModule1.qryEmployeeSalary do
  begin
    Close;
    strSQL := SQLBeforeClause(SQL.Text, 'ORDER BY');
    strSQL := SQLBeforeClause(strSQL, 'WHERE');
    if strFilter <> '' then
      strFilter := 'WHERE ' + strFilter;
    strSQL := strSQL + strFilter +
              ' ORDER BY PAY_SALARY."YEAR", PAY_SALARY."MONTH", EMPLOYEE.NAME';
    SQL.Clear;
    SQL.Add(strSQL);
    if cbFilterByYear.Checked then
      Params.ParamByName('Year').AsString := txtYearFilter.Text;
    if cbFilterByMonth.Checked then
      Params.ParamByName('Month').AsString := txtMonthFilter.Text;
    if not Prepared then
      Prepare;
    Open;
  end;
//  ClearDetails;
end;

procedure TfmSalary.SetPrintFilter; //seperate view and print dataset
var
  strSQL: string;
begin
  IBQuery1.Active := False;
  if cbFilterByYear.Checked or cbFilterByMonth.Checked then
  begin
    IBQuery1.SQL.Assign(DataModule1.qryEmployeeSalary.SQL);
    IBQuery1.Params.Assign(DataModule1.qryEmployeeSalary.Params);
  end else begin
    strSQL := SQLBeforeClause(DataModule1.qryEmployeeSalary.SQL.Text, 'ORDER BY');
    strSQL := SQLBeforeClause(strSQL, 'WHERE');
    strSQL := strSQL + ' WHERE PAY_SALARY.ID=:ID';
    IBQuery1.SQL.Clear;
    IBQuery1.SQL.Add(strSQL);
    IBQuery1.Params.ParamByName('ID').AsInteger := DataModule1.qryEmployeeSalary.FieldByName('PAY_ID').AsInteger;
  end;
  IBQuery1.Active := True;
end;

procedure TfmSalary.FillDetails;
var
  lVisible: boolean;
begin
  CurrentEmployeeID := DBGrid1.DataSource.DataSet.FieldByName('ID').AsInteger;
  if cbName.ListSource.DataSet.Locate('ID', CurrentEmployeeID, []) then
  begin
    cbName.Enabled := False;
    txtYear.Enabled := False;
    txtMonth.Enabled := False;
    UpDown1.Enabled := False;
    UpDown2.Enabled := False;
    cbName.KeyValue := cbName.ListSource.DataSet.FieldByName('NAME').AsString;
    txtYear.Text := DBGrid1.DataSource.DataSet.FieldByName('YEAR').AsString;
    txtMonth.Text := DBGrid1.DataSource.DataSet.FieldByName('MONTH').AsString;
    txtAdvanced.Text := DBGrid1.DataSource.DataSet.FieldByName('ADVANCED_PAY').AsString;
    txtAllowance.Text := DBGrid1.DataSource.DataSet.FieldByName('ALLOWANCE_PAY').AsString;
    txtWorkingDays.Text := DBGrid1.DataSource.DataSet.FieldByName('WORKING_DAYS').AsString;
    lblBasicSalary.Caption := cbName.ListSource.DataSet.FieldByName('BASE_PAY').AsString;
    lblNetPay.Caption := DBGrid1.DataSource.DataSet.FieldByName('NET_PAY').AsString;
    mmRemark.Lines.Text := DBGrid1.DataSource.DataSet.FieldByName('REMARK').AsString;

    lVisible := StrToBool(DBGrid1.DataSource.DataSet.FieldByName('EPF').AsString);
    lblEPFEmployee.Visible := lVisible;
    lblEPFEmployer.Visible := lVisible;
    Label5.Visible := lVisible;
    Label7.Visible := lVisible;

    lVisible := StrToBool(DBGrid1.DataSource.DataSet.FieldByName('SOCSO').AsString);
    lblSOCSOEmployee.Visible := lVisible;
    lblSOCSOEmployer.Visible := lVisible;
    Label8.Visible := lVisible;
    Label9.Visible := lVisible;

    Operation := opEdit;
  end else
    ShowMessage('Not found this employee in database, please check');
end;

procedure TfmSalary.InsertDetails;
begin
  try
    with DataModule1.qrySQL do
    begin
      SQL.Clear;
      Params.Clear;
      SQL.Add('insert into PAY_SALARY (EMPLOYEE_ID, "YEAR", "MONTH", BASE_PAY, ' +
              'EPF_EMPLOYEE, EPF_EMPLOYER, SOCSO_EMPLOYEE, SOCSO_EMPLOYER, ' +
              'ADVANCED_PAY, ALLOWANCE_PAY, PCB, NET_PAY, WORKING_DAYS, REMARK, CREATE_DATE, ' +
              'UPDATE_DATE) values (:EmployeeID, :Year, :Month, :BasePay, ' +
              ':EPFEmployee, :EPFEmployer, :SOCSOEmployee, :SOCSOEmployer, ' +
              ':AdvancedPay, :Allowance, :PCB, :NetPay, :WorkingDays, :Remark, CURRENT_TIMESTAMP, ' +
              'CURRENT_TIMESTAMP)');
      try
        Params.ParamByName('EmployeeID').AsInteger := CurrentEmployeeID;
        Params.ParamByName('Year').AsString := txtYear.Text;
        Params.ParamByName('Month').AsString := txtMonth.Text;
        Params.ParamByName('BasePay').AsString := lblBasicSalary.Caption;
        Params.ParamByName('EPFEmployee').AsString := lblEPFEmployee.Caption;
        Params.ParamByName('EPFEmployer').AsString := lblEPFEmployer.Caption;
        Params.ParamByName('SOCSOEmployee').AsString := lblSOCSOEmployee.Caption;
        Params.ParamByName('SOCSOEmployer').AsString := lblSOCSOEmployer.Caption;
        Params.ParamByName('AdvancedPay').AsString := txtAdvanced.Text;
        Params.ParamByName('Allowance').AsString := txtAllowance.Text;
        Params.ParamByName('PCB').AsString := lblPCB.Caption;
        Params.ParamByName('NetPay').AsString := lblNetPay.Caption;
        Params.ParamByName('WorkingDays').AsString := txtWorkingDays.Text;
        Params.ParamByName('Remark').AsString := mmRemark.Lines.Text;
        if not Prepared then
          Prepare;
        ExecSQL;
        Transaction.Commit;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          if E.SQLCode = -803 then
            ShowMessage('Duplicate Name in given Year and Month, no record be saved.')
          else
            ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        end;
      end;
    end;
  finally
    with DataModule1 do
    begin
      with qryEmployeeSalary do
        if not Active then
          Open;
      with fbEmployee do
        if not Active then
          Open;
      with fbConfig do
        if not Active then
          Open;
    end;
  end;
end;

procedure TfmSalary.Label14Click(Sender: TObject);
begin
  cbFilterByYear.Checked := not cbFilterByYear.Checked;
end;

procedure TfmSalary.Label15Click(Sender: TObject);
begin
  cbFilterByMonth.Checked := not cbFilterByMonth.Checked;
end;

procedure TfmSalary.UpdateDetails;
begin
   try
    with DataModule1.qrySQL do
    begin
      try
        SQL.Clear;
        Params.Clear;
        SQL.Add('update PAY_SALARY set EMPLOYEE_ID=:EmployeeID, "YEAR"=:Year, ' +
                '"MONTH"=:Month, BASE_PAY=:BasePay, EPF_EMPLOYEE=:EPFEmployee, ' +
                'EPF_EMPLOYER=:EPFEmployer, SOCSO_EMPLOYEE=:SOCSOEmployee, ' +
                'SOCSO_EMPLOYER=:SOCSOEmployer, ADVANCED_PAY=:AdvancedPay, ' +
                'ALLOWANCE_PAY=:Allowance, PCB=:PCB, NET_PAY=:NetPay, ' +
                'WORKING_DAYS=:WorkingDays, REMARK=:Remark, ' +
                'UPDATE_DATE=CURRENT_TIMESTAMP where ID=:ID');
        Params.ParamByName('EmployeeID').AsInteger := CurrentEmployeeID;
        Params.ParamByName('Year').AsString := txtYear.Text;
        Params.ParamByName('Month').AsString := txtMonth.Text;
        Params.ParamByName('BasePay').AsString := lblBasicSalary.Caption;
        Params.ParamByName('EPFEmployee').AsString := lblEPFEmployee.Caption;
        Params.ParamByName('EPFEmployer').AsString := lblEPFEmployer.Caption;
        Params.ParamByName('SOCSOEmployee').AsString := lblSOCSOEmployee.Caption;
        Params.ParamByName('SOCSOEmployer').AsString := lblSOCSOEmployer.Caption;
        Params.ParamByName('AdvancedPay').AsString := txtAdvanced.Text;
        Params.ParamByName('Allowance').AsString := txtAllowance.Text;
        Params.ParamByName('PCB').AsString := lblPCB.Caption;
        Params.ParamByName('NetPay').AsString := lblNetPay.Caption;
        Params.ParamByName('WorkingDays').AsString := txtWorkingDays.Text;
        Params.ParamByName('Remark').AsString := mmRemark.Lines.Text;
        Params.ParamByName('ID').AsInteger := DBGrid1.DataSource.DataSet.FieldByName('PAY_ID').AsInteger;
        if not Prepared then
          Prepare;
        ExecSQL;
        Transaction.Commit;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          ShowMessage(Format('Update Details: IBErrorCode=%d, SQLCode=%d, Msg=%s', [E.IBErrorCode, E.SQLCode, E.Message]));
        end;
      end;
    end;
  finally
    with DataModule1 do
    begin
      with qryEmployeeSalary do
        if not Active then
          Open;
      with fbEmployee do
        if not Active then
          Open;
      with fbConfig do
        if not Active then
          Open;
    end;
  end;
end;

end.
